package com.sinopac.model.BatchFileWork;

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import netfusion.jbranch.database.DataAccessManager;
import netfusion.jbranch.utils.file.csv.CsvReader;

import com.sinopac.entity.CustomerApply.ReportVO;
import com.sinopac.model.CustomerApply.CustomerApplyModel;
import com.sinopac.model.core.ModelCore;
import com.sinopac.util.IdUtil;
import com.sinopac.util.PropsUtil;

import freemarker.template.utility.StringUtil;

public class BatchFileModel extends ModelCore{
	/**
	 * 取得CSV檔案的資料判斷後做處理
	 * @return
	 */
	public int csvReader(String fileName,List<ReportVO> reportList,String userId){
		DataAccessManager dam = new DataAccessManager();
		int index = 0;
		try {
			// 找出DB裡未申請過的c.customer_id及未使用的token_esn
			String sqlA = "SELECT c.customer_id FROM customers AS c "+
						  "LEFT JOIN token_customer AS tc ON c.customer_id = tc.related_to "+
						  "LEFT JOIN job_queues AS j ON c.customer_id = j.customer_id "+
                          "WHERE tc.token_esn is null AND c.is_group = '0' AND j.job_code is null";
			String sqlB = "SELECT t.token_esn FROM tokens AS t "+
						  "LEFT JOIN token_customer AS tc ON t.token_esn = tc.token_esn "+
						  "LEFT JOIN job_queues AS j ON t.token_esn = j.token_esn "+
						  "WHERE tc.related_to is null AND t.token_status = '00' AND j.job_code is null";
			
			dam.setQueryString(sqlA);
			List listCus = dam.batchQuery();
			dam = new DataAccessManager();
			dam.setQueryString(sqlB);
			List listToken = dam.batchQuery();			
			String filePath = PropsUtil.getConfig("batchFile.path");
			String path = filePath + fileName;
			CsvReader products = new CsvReader(path);		
			products.readHeaders();
			
			StringBuffer content = new StringBuffer();
			while (products.readRecord())
			{
				index++;
				String cusID = products.get("Customer ID");
				String tokenEsn = products.get("TOKEN ESN");
				tokenEsn = StringUtil.rightPad(tokenEsn, 16, " ");
//				System.out.println(cusID + " : " + tokenEsn + ":" + tokenEsn.length());
				if(listCus.contains(cusID) && listToken.contains(tokenEsn)){
//					flag = insertTokenCus(cusID, tokenEsn);
//					cam.getReport(cusID, tokenEsn, userId, reportList, date);
					confirmToken(cusID,tokenEsn,reportList);
				}else{
//					System.out.println("index: " + index);
					content.append("第" + index + "筆配發Token_esn失敗, Customer ID: " + cusID + ", Token_esn: " + tokenEsn);
					content.append("\r\n");
				}
			}

			// 如有配發失敗則寫入log存成txt檔
			if(content.length()>0){
				writeLog(path.replace("csv", "txt"), content.toString());
			}
			products.close();
			
			// 如有配發成功才將csv檔刪除
//			if(flag){
//				flag = deletejob_queues(job_id) > 0;
//			}
			
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return index;
	}
	
	/**
	 * 如果CSV檔案中有資料申請成功，則刪除job_queues裡面的CSV檔
	 * @return
	 */
	private int deletejob_queues(String job_id) throws SQLException{
		DataAccessManager dam = new DataAccessManager();
		StringBuffer sql_sb = new StringBuffer();
		sql_sb.append("DELETE FROM job_queues ");
		sql_sb.append("WHERE  job_id = :job_id ");
		dam.setQueryString(sql_sb.toString());
		dam.setObject("job_id", job_id);
		return dam.exeDelete();
	}
	
	public void confirmToken(String custId,String token_esn,List<ReportVO> reportList){
		ReportVO reportVO = new ReportVO();
		CustomerApplyModel cam = new CustomerApplyModel();
		reportVO.setCustId(custId);
		reportVO.setTokenEsn(token_esn);
		reportVO.setCustomerList(cam.getCustomerForm(custId));
//		reportVO.setGroupList(cam.getGroupList(custId));
		reportList.add(reportVO);
	}
	
	/**
	 * update Tokens 的狀態為00(未啟用)
	 */
	public void updateTokens(String token_esn){
		DataAccessManager dam = new DataAccessManager();
		String sql = "UPDATE Tokens SET token_status ='00' WHERE token_esn = :token_esn ";
		dam.setObject("token_esn", token_esn);
		dam.setQueryString(sql);
		try {
			dam.exeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 取得CSV檔案的筆數
	 * @return
	 */
	public int csvIndex(String path){
		int index = 0;
		try {			
			CsvReader products = new CsvReader(path);		
			products.readHeaders();			
			while (products.readRecord())
			{
				index++;
			}
//			System.out.println("index:" + index);
			products.close();			
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return index;
	}
	
	/**
	 * 寫入Job_queues
	 * @return
	 */
	public Boolean insertJobQs(String path, String userName){
		String fileName = path.substring(path.length()-12);
		Date date = new Date();
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String subdate = df.format(date);
//		String filePath = PropsUtil.getConfig("batchFile.path");
		int index = csvIndex(path);
		boolean flag = false;
		DataAccessManager dam = new DataAccessManager();
		String sql = "INSERT INTO Job_queues(job_id, job_code, batch_file, " +
					 "batch_amount, operator, submitted_time) "+ 
	                 "VALUES(:job_id, :job_code, :batch_file, :batch_amount, :operator, :submitted_time)";
		dam.setQueryString(sql);
		dam.setObject("job_id", IdUtil.getJob_id());
		dam.setObject("job_code", "2000");
		dam.setObject("batch_file", fileName);
		dam.setObject("batch_amount", index);
		dam.setObject("operator", userName);
		dam.setObject("submitted_time", subdate);
		try {
			flag = dam.exeUpdate()>0;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
	
	/**
	 * 寫入Token_Customer
	 * @return
	 */
	public Boolean insertTokenCus(String cusid, String token_esn){
		boolean flag = false;
		DataAccessManager dam = new DataAccessManager();
		String sql = "INSERT INTO Token_Customer(token_esn, related_to, is_related_to_group) " +
	                 "VALUES('" + token_esn + "', '" +  cusid + "', '0')";
		dam.setQueryString(sql);

		try {
			flag = dam.exeUpdate()>0;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}
	
	/**
	 * 產生批次檔失敗log
	 * @return
	 */
	public void writeLog(String filePath, String content) {
		try {
//			System.out.println("path: " + filePath + " " + content);
//			String filePath = path; // 檔案路徑 
			FileOutputStream fileOutputStream = new FileOutputStream(filePath); 
			OutputStreamWriter outputStreamWriter = new OutputStreamWriter(fileOutputStream); 
			outputStreamWriter.write(content); 
			outputStreamWriter.close(); 
		} catch (Exception e) { 
			e.printStackTrace(); 
		} 
	} 

}
